<?php
require_once 'Service/DataSource.php';

class Service_DataSource_ZendDb extends Service_DataSource
{
    protected $_adapter = null;

    protected $_conditionPrefix = 'Service_Condition_ZendDb';

    public function getItem($spec, $id, $attrList = null)
    {
        if (empty($attrList)) {
            $attrList = '*';
        }

        $db = $this->getAdapter();
        
        $select = $this->_getSpecSelect($spec, $attrList);

        if ($id) {
            $alias = '';
            if (is_array($spec)) {
                $alias = $spec['structure']['0']['name'].'.';
            }
            foreach ($id as $key => $value) {
                $select->where($db->quoteIdentifier($alias.$key) . '=' . $db->quote($value));
            }

            $items = $select->query()->fetchAll();

            if (count($items) == 1) {
                return current($items);
            } elseif (count($items) > 1) {
                require_once 'Service/DataSource/Exception.php';
                throw new Service_DataSource_Exception('DataSource Error: Single item expected but multiple items found.');
            }
        }

        return null;
    }

    public function removeItem($spec, $id)
    {
        $db = $this->getAdapter();

        $where = array();
        foreach ($id as $key => $value) {
            $where[] = $db->quoteIdentifier($key) . '=' . $db->quote($value);
        }
        
        return $db->delete($this->_options['prefix'] . $spec, $where);
    }

    public function putItem($spec, $id, $item)
    {
        $db = $this->getAdapter();

        if ($id) {
            $where = array();
            foreach ($id as $key => $value) {
                $where[] = $db->quoteIdentifier($key) . '=' . $db->quote($value);
            }
            $existed = $this->getItem($spec, $id);
        }
        
        if (isset($existed)) {
            return $db->update($this->_options['prefix'] . $spec, $item, $where);
        } else {
            $transData = $db->insert($this->_options['prefix'] . $spec, $item);
            return $db->lastInsertId();
        }
    }
    
    /**
     * update items
     *
     * @param string $spec
     * @param array $fields
     * @param array $filters
     * 
     * @return integer
     */
    public function updateItems($spec, $fields, $filters = array())
    {
        $db = $this->getAdapter();
        
        return $db->update($this->_options['prefix'] . $spec, $fields, 
            $this->_filtersToWhere($filters));
    }
    
    public function removeItems($spec, $filters = array())
    {
        $db = $this->getAdapter();
         
        $db->delete($this->_options['prefix'] . $spec, $this->_filtersToWhere($filters));
    }

    public function listItems($spec, $attrList = null, $limit = null,
        $offset = null, $sort = array(), $filters = array())
    {
        if (empty($attrList)) {
            $attrList = array();
        }

        $select = $this->_getSpecSelect($spec, $attrList);

        // FIXME distinct directive may produce some bugs
        $select->distinct();
        $select->limit($limit, $offset);

        if ($filters instanceof Service_Condition) {
            $filters = (string) $filters;

            if (!empty($filters)) {
                $select->where($filters);
            }
        } elseif (count($filters)) {

            foreach ($filters as $filter) {
                if (count($filter) == 3) {
                    $select->where($this->_processFilter($filter));
                }
            }
        }
        if (count($sort)) {
            $select->order($sort);
        }

        return $select->query()->fetchAll();
    }

    public function countItems($spec, $filters = array())
    {
        $db = $this->getAdapter();

        if (is_array($spec)) {

            if (!is_array($spec['identifier'])) {
                $spec['identifier'] = array($spec['identifier']);
            }

            $identifier = array();

            foreach ($spec['structure'] as $table) {
                
                if (is_array($table["name"])) {
                    $tableAlias = key($table["name"]);
                    $tableName = $table["name"][$tableAlias];
                } else {
                    $tableAlias = $tableName = (string) $table["name"];
                }

                if (isset($table['fields'])) {
                    foreach ($table['fields'] as $key => $columnName) {
                        if (is_numeric($key)) {
                            $columnAlias = $columnName;
                        } else {
                            $columnAlias = $key;
                        }
                        if (in_array($columnAlias, $spec['identifier'])) {
                            $identifier[] = $db->quoteIdentifier(
                                    $tableAlias . '.' . $columnName);
                        }

                    }
                }
            }

            $identifier = implode(', ', $identifier);
            
            $columns = array("items_count" => "COUNT(distinct {$identifier})");
        } else {
            $columns = array("items_count" => "COUNT(*)");
        }

        $select = $this->_getSpecSelect($spec)->columns($columns);

        if ($filters instanceof Service_Condition) {
            $filters = (string) $filters;

            if (!empty($filters)) {
                $select->where($filters);
            }
        } elseif (count($filters)) {
            foreach ($filters as $filter) {
                if (count($filter) == 3) {
                    $select->where($this->_processFilter($filter));
                }
            }
        }

        $items = $select->query()->fetchAll();
        if (count($items) == 1) {
            
            $item = current($items); 
            return $item['items_count'];
            
        } elseif (count($items) > 1) {
            require_once 'Service/DataSource/Exception.php';
            throw new Service_DataSource_Exception('DataSource Error: Single item expected but multiple items found.');
        } else {
            require_once 'Service/DataSource/Exception.php';
            throw new Service_DataSource_Exception('DataSource Error: Single item expected but no items found.');
        }
        
    }
    
    /**
     * Returns DB adapter.
     *
     * @return Zend_Db_Adapter_Abstract
     */
    public function getAdapter()
    {
        if (null === $this->_adapter) {
            require_once 'Zend/Db.php';
            $this->_adapter = Zend_Db::factory($this->_options['adapter'],
                $this->_options['params']);
            $this->_adapter->query('SET NAMES utf8');
        }

        return $this->_adapter;
    }
    
    /**
     * Filters to where format
     *
     * @param unknown_type $filters
     * @return mixed
     */
    private function _filtersToWhere($filters)
    {
        $db = $this->getAdapter();
        $where = "";
        
        if (count($filters)) {
            $where = array();
            foreach ($filters as $filter) {
                $where[] = $this->_processFilter($filter);
            }
        }

        return $where;
    }

    protected function _processFilter($question)
    {
        list($field, $operator, $value) = $question;

        $db = $this->getAdapter();

        switch($operator) {
            case 'not in':
            case 'in':

                if (is_array($value) && count($value)) {
                    $ids = '(';
                    foreach ($value as $item) {
                        $ids .= $db->quote($item) . ',';
                    }

                    $ids[strlen($ids) - 1] = ')';

                    $value = $ids;
                } else {
                    $value = $db->quote($value);
                }

                $field = $db->quoteIdentifier($field);

                break;
            case 'is':
                $field = $db->quoteIdentifier($field);
                $value = " ".$value;
                break;
            default:
                $field = $db->quoteIdentifier($field);
                $value = $db->quote($value);
        }

        return $field . $operator . $value;
    }

    public function _getSpecSelect($spec, $attrList = array())
    {
        $db = $this->getAdapter();

        $select = $db->select();

        if (is_array($spec)) {

            foreach($spec['structure'] as $table) {
                
                if (is_array($table["name"])) {
                    $tableAlias = key($table["name"]);
                    $tableName = $table["name"][$tableAlias];
                } else {
                    $tableAlias = $tableName = (string) $table["name"];
                }
              
                $cols = array();
                if (isset($table["fields"]) && is_array($table["fields"])) {
                    foreach ($table["fields"] as $key => $columnName) {
                        if (is_numeric($key)) {
                            $columnAlias = $columnName;
                        } else {
                            $columnAlias = $key;
                        }
 
                        if (in_array($columnAlias, $attrList) || empty($attrList)) {
                            $cols[$columnAlias] = $tableAlias . '.' . $columnName;
                        }
                    }
                } else {
                    $cols = '*';
                }

                if (isset($table["relations"])) {
                    
                    if (is_string($table["relations"])) {
                        $condition = $table["relations"];
                    } elseif (is_array($table["relations"])) {
                        $condition = array();
                        foreach ($table["relations"] as $mainField => $relField) {
                            $condition[] = $db->quoteIdentifier($mainField)
                                . " = " . $db->quoteIdentifier($relField);
                        }
                        
                        $condition = implode(' AND ', $condition);
                    }
                    
                    $select->joinLeft(
                        array($tableAlias => $this->_options['prefix'] . $tableName),
                        $condition, null
                    );

                } else {
                    $select->from(
                        array($tableAlias => $this->_options['prefix'] . $tableName), $cols
                    );
                }
                $select->columns($cols, $tableAlias);
            }
        } else {
            $select->from($this->_options['prefix'] . $spec, empty($attrList)?'*':$attrList);
        }

        return $select;
    }
}
